package bridge;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseBuilder {
	private Connection conn;

	public DatabaseBuilder() throws SQLException {
		this("jdbc:mysql://localhost/sensor_data", "root", "");
	}

	public DatabaseBuilder(String url, String user, String pass) throws SQLException {

		try { Class.forName ("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) {e.printStackTrace();}

		conn = DriverManager.getConnection(url, user, pass);
		if (conn != null) {
			System.out.println("Database Connection established");
		} else {
			System.out.println("Database Connection failed");
		}

		System.out.println("Database initialized.");
	}

	public void init() throws SQLException {
		initTables(conn);

		initSensorTypes(conn);
		initSensors(conn);
		initSensorDescriptions(conn);
	//	initData(conn);
	}

	private void initSensors(Connection conn) throws SQLException {
		Statement stmt = conn.createStatement();
		
		// sensorID = (spotID << 3) + sensorIndex
		stmt.execute("INSERT INTO Sensors VALUES "
				+		"(0, 'Accelerometer', 'Awesome accelerometer #1 (A0)'),"
				+		"(1, 'Accelerometer', 'Awful accelerometer (A1)'),"
				+		"(16, 'Accelerometer', 'Accelerometer'),"
				+		"(17, 'Accelerometer', 'Accelerometer'),"
				+		"(8, 'Strain Gauge', 'Strain Gauge'),"
				+		"(24, 'Strain Gauge', 'Strain Gauge'),"
				+		"(18, 'Thermometer', 'Thermometer'),"
				+		"(19, 'Humidity Sensor', 'Humidity Sensor'),"
				+		"(20, 'Wind Direction', 'Wind Direction'),"
				+		"(21, 'Wind Speed', 'Wind Speed')");
				
			
		stmt.close();
	}

	private void initSensorTypes(Connection conn) throws SQLException {
		Statement stmt = conn.createStatement();
		
		stmt.execute("INSERT INTO SensorTypes VALUES "
				+		"('Accelerometer', -6, 6, 'G',2000,60000),"
				+		"('Strain Gauge', 0, 765.6, 'microstrain',2000,60000),"
				+		"('Thermometer', 0, 100, 'F',10000,3600000),"
				+		"('Wind Direction', 0, 360, 'Degrees',10000,3600000),"
				+		"('Wind Speed', 0, 120, 'MPH',10000,3600000),"
				+		"('Humidity Sensor', 0, 100, '%',10000,3600000)");
		
		stmt.close();
	}
	
	private void initSensorDescriptions(Connection conn) throws SQLException {
		Statement stmt = conn.createStatement();
		
		stmt.execute("INSERT INTO SensorDescriptions VALUES "
				+		"(0, 'This accelerometer measures the transverse vibrations across the bridge.  The sensor is located onboard the Sun SPOT at the middle of the bridge.'),"
				+		"(1, 'This accelerometer measures the vertical vibration of the walkway.  The sensor is located onboard the Sun SPOT at the middle of the bridge.'),"
				+		"(8, 'This strain gauge measures the stress on the NORTH OR SOUTH arch.  The greater the stress, the greater the load on the bridge.'),"
				+		"(16, 'This accelerometer measures the vertical vibrations at the top of the NORTH OR SOUTH arch.'),"
				+		"(17, 'This accelerometer measures the transverse vibrations at the top of the NORHT OR SOUTH arch.'),"
				+		"(18, 'This sensor measures that measures the ambient average kinetic energy (the temperature) found at the bridge.'),"
				+		"(19, 'This sensor measures the humidity at the bridge site.'),"
				+		"(20, 'This sensor measures the wind direction in terms of the degree offset from north.'),"
				+		"(21, 'This sensor measures the wind speed in MPH at the bridge site.'),"
				+		"(24, 'This strain gauge measures the stress on the NORTH OR SOUTH arch.')");
		
		stmt.close();
	}

	/**
	 * Creates the tables if needed
	 */
	private void initTables(Connection conn) throws SQLException {
		Statement stmt = conn.createStatement();

		stmt.execute("DROP TABLE IF EXISTS Sensors");
		stmt.execute("DROP TABLE IF EXISTS SensorTypes");
		//create a sepearte table for descriptions because:
		// chart.jsp calls get sensor on every page load
		// if we put the description in the sensor table (and thus the sensor object)
		// we would be requesting the description from the MySQL database everytime the chart refreshes
		// if we keep the description seperate, than it will only need to load once from visualization.jsp
		stmt.execute("DROP TABLE IF EXISTS SensorDescriptions");
		stmt.execute("DROP TABLE IF EXISTS Data");

		stmt.execute(	"CREATE TABLE IF NOT EXISTS Sensors ("
				+			"sensorID TINYINT,"
				+			"type CHAR(16),"
				+			"name VARCHAR(32)"
				+		")");
		
		stmt.execute(	"CREATE TABLE IF NOT EXISTS SensorTypes ("
				+			"type CHAR(16),"
				+			"min FLOAT,"
				+			"max FLOAT,"
				+			"unit CHAR(16),"
				+			"refresh INT,"
				+			"window INT"
				+		")");

		stmt.execute(	"CREATE TABLE IF NOT EXISTS SensorDescriptions ("
				+			"sensorID TINYINT,"
				+			"description VARCHAR(1024)"
				+		")");
		/* 
		 * We need to use the MyISAM storage engine here. For whatever reason, MyISAM is much faster
		 * than InnoDB, as it accesses the hard disk much less frequently. If we use InnoDB, at least
		 * on this laptop I'm testing on, the database gets behind at even a relatively slow data
		 * rate (like 50hz or something).
		 */    	
		stmt.execute(	"CREATE TABLE IF NOT EXISTS Data ("
				+			"sensorID TINYINT,"
				+			"time BIGINT,"
				+			"value FLOAT"
				+		") ENGINE = 'MyISAM'");

		stmt.close();
	}

	private void initData(Connection conn) throws SQLException
	{
		Statement stmt = conn.createStatement();
		int points = 150;
		float[] data = new float[points];
		for(int k = 0; k < points; k++)
		{
			data[k] = (float)(Math.sin(2.0 * 3.14159 * k/((double)points - 1.0)) + 1D)/2F;
		}
		
		for(int k = 0; k < data.length; k++)
		{
			String statement = "INSERT INTO Data Values " + "(0, " + k + ", " + data[k] + ")";
			stmt.execute(statement);
		}
		
		for(int k = 0; k < points; k++)
		{
			data[k] = (float)(Math.sin(3.0 * 3.14159 * k/((double)points - 1.0)) + 1D)/2F;
		}
		
		for(int k = 0; k < data.length; k++)
		{
			String statement = "INSERT INTO Data Values " + "(1, " + k + ", " + data[k] + ")";
			stmt.execute(statement);
		}
		
		
		for(int k = 0; k < data.length; k++)
		{
			String statement = "INSERT INTO Data Values " + "(2, " + k + ", " + Math.random() + ")";
			stmt.execute(statement);
		}
		
		stmt.close();
	}
	
	public String dump() throws SQLException {
		StringBuffer buf = new StringBuffer();
		
		Statement stmt = conn.createStatement();
		
		ResultSet set = stmt.executeQuery("SELECT * FROM Sensors");
		buf.append("Sensors table:\n");
		while (set.next()) {
			buf.append("name: " + set.getString("name") + ",");
			buf.append("type: " + set.getString("type") + ",");
			buf.append("sensorID: " + set.getString("sensorID") + "\n");
		}

		set = stmt.executeQuery("SELECT * FROM SensorTypes");
		buf.append("SensorTypes table:\n");
		while (set.next()) {
			buf.append("type: " + set.getString("type") + ",");
			buf.append("min: " + set.getFloat("min") + ",");
			buf.append("max: " + set.getFloat("max") + "\n");
		}
		
		set = stmt.executeQuery("SELECT * FROM SensorDescriptions");
		buf.append("SensorDescriptions table:\n");
		while (set.next()) {
			buf.append("sensorID: " + set.getString("sensorID") + ",");
			buf.append("description: " + set.getString("description") + "\n");
		}
		
		set = stmt.executeQuery("SELECT * FROM Data");
		buf.append("Data table:\n");
		while(set.next())
		{
			buf.append("sensorID: " + set.getString("sensorID") + ",");
			buf.append("time: " + set.getLong("time") + ",");
			buf.append("value: " + set.getFloat("value") + "\n");		
		}
		
		
		return buf.toString();
	}
}
